unit UFlyLabelTicket;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, Buttons, StdCtrls, Spin, DB, ADODB, UFlyLabelDB;

type
  TFFlyLabelTicket = class(TForm)
    Label1: TLabel;
    EYear: TSpinEdit;
    Label2: TLabel;
    EDir: TEdit;
    SpeedButton1: TSpeedButton;
    ProgressBar1: TProgressBar;
    Label3: TLabel;
    ProgressBar2: TProgressBar;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    DCInsert: TADOCommand;
    Memo1: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
  private
    { Private declarations }
    procedure CheckDB(Area: string);
  public
    { Public declarations }
  end;

var
  FFlyLabelTicket: TFFlyLabelTicket;

implementation

uses DateUtils, FileCtrl;

{$R *.dfm}

procedure TFFlyLabelTicket.CheckDB(Area: string);
var
  Sql: string;
begin
  Sql:= 'if not exists (select 1 from  sysobjects where  id = object_id(''Ring_Label' +
    IntToStr(EYear.Value) + '_' + Area + ''') and type = ''U'')' +
    'CREATE TABLE [Ring_Label' + IntToStr(EYear.Value) + '_' + Area + '] (' +
	  '[CodeNo] [int] NOT NULL , [Captcha] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,' +
	  '[SuccessCnt] [int] NULL , [FailedCnt] [int] NULL , ' +
    '[AccessLog] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,' +
	  'CONSTRAINT [PK_LABEL_' + IntToStr(EYear.Value) + '_' + Area + '] PRIMARY KEY  CLUSTERED ' +
	  '([CodeNo])ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]';
  DCInsert.Connection.Execute(Sql);
  DCInsert.CommandText:= 'if not exists (select 1 from [Ring_Label' + IntToStr(EYear.Value) + '_' + Area +
    ']WHERE CodeNo = :ONO)INSERT INTO [Ring_Label' + IntToStr(EYear.Value) + '_' + Area +
    ']([CodeNo], [Captcha], [SuccessCnt], [FailedCnt], [AccessLog])VALUES(:CNO,:CPT, 0, 0, '''')';
end;

procedure TFFlyLabelTicket.FormCreate(Sender: TObject);
begin
  EYear.Value:= YearOf(Now) + 1;
  EDir.Text:= ExtractFilePath(Application.ExeName) + 'Data';
end;

procedure TFFlyLabelTicket.SpeedButton1Click(Sender: TObject);
var
  d: string;
begin
  if SelectDirectory(d, [], 0) then
    EDir.Text:= d;
end;

procedure TFFlyLabelTicket.SpeedButton2Click(Sender: TObject);
var
  F : TSearchRec;
  Found : Boolean;
  fs: TextFile;
  S, Sn, Sv: string;
  mPos, iFile, iCode: Integer;

  function IncPosition(P: TProgressBar; I, rate: Integer): Integer;
  begin
    P.Position:= Round(I / (I / rate + 1) * rate);
    P.Repaint;
    Result:= I + 1;
  end;
begin
  Found := (FindFirst(EDir.Text + '\*.txt', faAnyFile, F) = 0);
  iFile:= IncPosition(ProgressBar1, 0, 10);
  while Found do begin
    AssignFile(fs, EDir.Text + '\' + F.Name);
    Reset(fs);
    CheckDB(ChangeFileExt(F.Name, ''));
    iCode:= IncPosition(ProgressBar2, 0, 1000000);
    while not EOF(fs) do begin
      Readln(fs, S);
      mPos:= Pos(' ', S);
      Sn:= Copy(S, 1, mPos - 1);
      Sv:= Copy(S, mPos + 1, MaxInt);
      if Sv = 'JYM' then Continue;
      with DCInsert do try
        Parameters[0].Value:= StrToInt(Sn);
        Parameters[1].Value:= StrToInt(Sn);
        Parameters[2].Value:= Sv;
        Execute;
      except
        on E: Exception do
          Memo1.Lines.Add('Err!' + IntToStr(EYear.Value) + ChangeFileExt(F.Name, '') + Sn + ':' + Sv + '[' + E.Message + ']');
      end;
      iCode:= IncPosition(ProgressBar2, iCode, 1000000);
    end;
    CloseFile(fs);
    Found := (FindNext(F) = 0);
    iFile:= IncPosition(ProgressBar1, iFile, 10);
  end;
  FindClose(F);
  ProgressBar1.Position:= 0;
  ProgressBar2.Position:= 0;
end;

procedure TFFlyLabelTicket.SpeedButton3Click(Sender: TObject);
begin
  with DCInsert do try
        Parameters[0].Value:= 1;
        Parameters[1].Value:= 1;
        Parameters[2].Value:= 22222;
        Execute;
      except
        Memo1.Lines.Add('Err!' + IntToStr(EYear.Value));
      end;
end;

end.
